import pymysql
import xlwt

def exe_db():
    # 打开数据库连接，不指定数据库
    conn = pymysql.connect(host='localhost', port=3306, user='root', password='xuhuan.5130638')
    # conn = pymysql.connect(host='139.155.88.162', port=3306, user='root', password='xuhuan.59')
    conn.select_db('linjiashop_backups')
    cur = conn.cursor()  # 获取游标

    # ================
    # 数据记录
    id_user_list = []
    phone_list = []
    name_list = []
    addr_list = []
    order_list = []
    total_money_list = []
    time_list = []
    c_list = []
    user_p_list = []

    try:
        # print("===========================================================================================================")
        print("通知系统开始查询订单")
        cur.execute("select * from t_shop_order where (status>1 and status<=4)")  # 1未付款 2未发货 3已发货 4已完成
        # 使用 fetchone() 方法获取一条数据
        data = cur.fetchall()

        if data:
            for row in data:
                if row[10] not in id_user_list:
                    id_user_list.append(row[10])
                    phone_list.append(row[12])
                    time_list.append(row[16])
                    name_list.append(row[5])
                    addr_list.append(row[6])
                    order_list.append(row[13])
                    total_money_list.append(row[23])
                    c_list.append(row[7])
                    user_p_list.append(row[26])
                else:
                    money = total_money_list[id_user_list.index(row[10])]
                    total_money_list[id_user_list.index(row[10])] = float(money) + float(row[23])
                    if row[7] != None:
                        money1 = c_list[id_user_list.index(row[10])]
                        if money1 == None:
                            c_list[id_user_list.index(row[10])] = row[7]
                        else:
                            c_list[id_user_list.index(row[10])] = float(money1) + float(row[7])
                    if row[26] != None:
                        money2 = user_p_list[id_user_list.index(row[10])]
                        if money2 == None:
                            user_p_list[id_user_list.index(row[10])] = row[26]
                        else:
                            user_p_list[id_user_list.index(row[10])] = float(money2) + float(row[26])

            for index in range(len(total_money_list)):
                total_money_list[index] = float(total_money_list[index])/100

            # 优惠金额
            for index in range(len(c_list)):
                if c_list[index] != None:
                    c_list[index] = float(c_list[index])/100

            # 用户余额抵扣金额
            for index in range(len(user_p_list)):
                if user_p_list[index] != None:
                    user_p_list[index] = float(user_p_list[index])/100
            # 生成报表
            work_book = xlwt.Workbook(encoding='utf-8')
            sheet = work_book.add_sheet('sheet表名')
            sheet.write(0, 0, '用户ID')
            sheet.write(0, 1, '用户手机')
            sheet.write(0, 2, '时间')
            sheet.write(0, 3, '名称')
            sheet.write(0, 4, '地址')
            sheet.write(0, 5, '订单号')
            sheet.write(0, 6, '实际消费金额')
            sheet.write(0, 7, '优惠金额')
            sheet.write(0, 8, '余额抵扣金额')
            for index in range(len(id_user_list)):
                sheet.write(index+1, 0, id_user_list[index])
                sheet.write(index+1, 1, phone_list[index])
                sheet.write(index+1, 2, time_list[index])
                sheet.write(index+1, 3, name_list[index])
                sheet.write(index+1, 4, addr_list[index])
                sheet.write(index+1, 5, order_list[index])
                sheet.write(index+1, 6, total_money_list[index])
                sheet.write(index+1, 7, c_list[index])
                sheet.write(index+1, 8, user_p_list[index])
            work_book.save('C:\\Users\\Administrator\\Desktop\\用户消费统计.xls')
        conn.commit()
    finally:
        try:
            cur.close()
            conn.close()
        except Exception as e1:
            print('sql执行成功异常：' + str(e1))


if __name__ == '__main__':
    exe_db()